﻿using Dapper;
using Domain.Entity.Basic_information;
using Domain.Entity.Financial_Management;
using Domain.Entity.System_operation;
using Domain.Entity.System_Ordermanagement;
using Domain.IRepository.Financial_Management;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Volo.Abp.DependencyInjection;

namespace EFCore.Repository.Financial_Management
{
    [Dependency(ServiceLifetime.Scoped)]
    public class Payable_meterRepository : IPayable_meterRepository
    {
        public MyDbContext myDbContext { get; set; }

        public Payable_meterRepository(MyDbContext myDbContext)
        {
            this.myDbContext = myDbContext;
        }
        public int Count(string? Billing_status, string? Origin_place, string? Det_place, string? Sign_time)
        {
            string sql = @"select A.Payable_number,B.Wb_number,C.Cr_cp_name,B.Wb_status,D.Sign_time,
A.Billing_status,A.Billing_time,A.Payable_reconciliation,A.Payable_reconciliation_number,B.Freight_count,B.Packag_fee,B.Handling_fee,
B.Pay_as_you_go,B.Prepayment,B.Collect,B.Periodical_payment,A.[Reconciled_amounts],
A.[UnReconciled_amounts],A.[Chargebacks_amounts],B.Reality_time,B.Origin_place,B.Det_place,
E.Dv_name,E.Dv_phone,B.Create_time,B.Create_by from [dbo].[Payable_meter] A join Wb_meter B on A.Wb_id=B.Wb_id
join [dbo].[Carrier_ment] C ON A.Cr_id=C.Cr_id
join [dbo].[Signature_receipt] D ON A.Sign_id=D.Sign_id
join [dbo].[Drivers_ment] E ON A.Dv_id=E.DV_id";
            if (Sign_time != null && Sign_time != "")
            {
                string[] time1 = Sign_time.Split(',');
                sql += $" and [Sign_time]>='{time1[0]}' and [Sign_time]<='{time1[1]}'";
            }
            if (Billing_status != null && Billing_status != "")
            {
                sql += $"AND (Billing_status LIKE '%{Billing_status}%')";
            }
            if (Origin_place != null && Origin_place != "")
            {
                sql += $" and [Origin_place] like '%{Origin_place}%'";
            }
            if (Det_place != null && Det_place != "")
            {
                sql += $" and [Det_place] like '%{Det_place}%'";
            }
            int count = myDbContext.Database.GetDbConnection().Query<Payable_meter>(sql).Count();
            return count;
        }

        public Carrier_ment GetCarrier_id(int id)
        {
            string sql = $"SELECT * FROM [dbo].[Carrier_ment] where [Cr_id]={id}";
            Carrier_ment zhi = myDbContext.Database.GetDbConnection().Query<Carrier_ment>(sql).FirstOrDefault();
            return zhi;
        }

        public Drivers_ment GetDrivers_id(int id)
        {
            string sql = $"SELECT * FROM [dbo].[Drivers_ment] where [Dv_id]={id}";
            Drivers_ment zhi = myDbContext.Database.GetDbConnection().Query<Drivers_ment>(sql).FirstOrDefault();
            return zhi;
        }

        public Wb_meter GetWb_id(int id)
        {
            string sql = $"SELECT * FROM [dbo].[Wb_meter] where [Wb_id]={id}";
            Wb_meter zhi = myDbContext.Database.GetDbConnection().Query<Wb_meter>(sql).FirstOrDefault();
            return zhi;
        }

        public List<Payable_meter> GetPayable_meter()
        {
            string sql = "SELECT * FROM [dbo].[Payable_meter]";
            List<Payable_meter> zhi = myDbContext.Database.GetDbConnection().Query<Payable_meter>(sql).ToList();
            return zhi;
        }

        public Signature_receipt GetSign_id(int id)
        {
            string sql = $"SELECT * FROM [dbo].[Signature_receipt] where [Sign_id]={id}";
            Signature_receipt zhi = myDbContext.Database.GetDbConnection().Query<Signature_receipt>(sql).FirstOrDefault();
            return zhi;
        }

        public List<Payable_meter> GetPayable_meter_Update(int Payable_id)
        {
            string sql = $@"select * from [dbo].[Payable_meter] where Payable_id='{Payable_id}'";
            return myDbContext.Database.GetDbConnection().Query<Payable_meter>(sql).ToList();
        }

        public int Update(Payable_meter payable_meter)
        {
            string sql = $@"UPDATE [dbo].[Payable_meter] SET [Actual_payment_Pay] = '{payable_meter.Actual_payment_Pay}',[Actual_payment_prepayment] ='{payable_meter.Actual_payment_prepayment}',[Actual_payment_collect] = '{payable_meter.Actual_payment_collect}',[Actual_payment_periodical] = '{payable_meter.Actual_payment_periodical}' WHERE Payable_id={payable_meter.Payable_id}";
            return myDbContext.Database.GetDbConnection().Execute(sql);
        }
    }
}
